package edu.example.student.dao;

import edu.example.student.beans.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    public List<Student> getList(Integer pagenum,Integer totalnum)
    {
        DataSource dataSource=C3p0Utils.getDataSource();
        QueryRunner queryRunner=new QueryRunner(dataSource);
        String sql="select stucode,name,gender,password,phone,buildings_card,rooms_card from user where role=0 limit ?, ?";
        BeanListHandler beanListHandler=new BeanListHandler(Student.class);
        List<Student> list=null;
        try {
            list=(ArrayList<Student>)queryRunner.query(sql,beanListHandler, pagenum, totalnum);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public List<Student> selectList(String building,String stuname)
    {
        DataSource dataSource= C3p0Utils.getDataSource();
        QueryRunner queryRunner=new QueryRunner(dataSource);
        String sql="select stucode,name,gender,password,phone,buildings_card,rooms_card from user where (buildings_card=? or ? = '') and name=?";
        BeanListHandler beanListHandler=new BeanListHandler(Student.class);
        List<Student> list=null;
        try {
            list= (List<Student>) queryRunner.query(sql, beanListHandler,building,building,stuname);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public Integer updateList(Integer stuID,String password,String stuname,String phone)
    {
        DataSource dataSource=C3p0Utils.getDataSource();
        QueryRunner queryRunner=new QueryRunner(dataSource);
        String sql="update user set password=?,name=?,phone=? where stucode=?";
        int row=-1;
        try {
            row=queryRunner.update(sql,password,stuname,phone,stuID);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return row;
    }
    public List<Student> selects(Integer id)
    {
        DataSource dataSource= C3p0Utils.getDataSource();
        QueryRunner queryRunner=new QueryRunner(dataSource);
        String sql="select stucode,name,gender,password,phone,buildings_card,rooms_card from user where stucode=?";
        BeanListHandler beanListHandler=new BeanListHandler(Student.class);
        List<Student> list=null;
        try {
            list= (List<Student>) queryRunner.query(sql, beanListHandler,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public Integer delete(Integer id)
    {
        DataSource dataSource=C3p0Utils.getDataSource();
        QueryRunner queryRunner=new QueryRunner(dataSource);
        String sql="delete from user where stucode=?";
        int row=0;
        try {
            row=queryRunner.update(sql,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return row;
    }
    public List<Student> selbuidling(String building)
    {
        DataSource dataSource=C3p0Utils.getDataSource();
        QueryRunner queryRunner=new QueryRunner(dataSource);
        String sql="select stucode,name,gender,password,phone,buildings_card,rooms_card from user where (buildings_card=? or ? = '') and role=0";
        BeanListHandler beanListHandler=new BeanListHandler(Student.class);
        List<Student> list=null;
        try {
            list= (List<Student>) queryRunner.query(sql,beanListHandler,building,building);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public List<Student> selsex(String building,String sex)
    {
        DataSource dataSource=C3p0Utils.getDataSource();
        QueryRunner queryRunner=new QueryRunner(dataSource);
        String sql="select stucode,name,gender,password,phone,buildings_card,rooms_card from user where (buildings_card=? or ? = '') and gender=? and role=0";
        BeanListHandler beanListHandler=new BeanListHandler(Student.class);
        List<Student> list=null;
        try {
            list= (List<Student>) queryRunner.query(sql,beanListHandler,building,building,sex);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public List<Student> selroom(String building,String rooms)
    {
        DataSource dataSource=C3p0Utils.getDataSource();
        QueryRunner queryRunner=new QueryRunner(dataSource);
        String sql="select stucode,name,gender,password,phone,buildings_card,rooms_card from user where (buildings_card=? or ? = '') and rooms_card=? and role=0";
        BeanListHandler beanListHandler=new BeanListHandler(Student.class);
        List<Student> list=null;
        try {
            list= (List<Student>) queryRunner.query(sql,beanListHandler,building,building,rooms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
}
